package ua.kiev.tosik.price.modules.clients.tools;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.*;
import ua.kiev.tosik.price.modules.clients.models.ExcelData;

import java.io.*;
import java.text.MessageFormat;
import java.util.*;

public class ExcelParser {

	private static Logger logger = Logger.getLogger(ExcelParser.class);
	private InputStream inputStream;

	public ExcelParser(byte[] excelFile) {
		inputStream = new ByteArrayInputStream(excelFile);
	}

	public Vector<ExcelData> parse() throws Exception {
		logger.debug("Parse excel file");
		Workbook wb = WorkbookFactory.create(inputStream);
		logger.debug("Tabs in Excel: " + wb.getNumberOfSheets());
		Vector<ExcelData> allData = new Vector<ExcelData>();
		int rowCount = 0;
		for ( int i = 0; i < wb.getNumberOfSheets(); i++ ) {
			ExcelData data = getDataFromTab(wb.getSheetAt(i));
			allData.add(data);
			rowCount += data.getData().size();
		}
		logger.debug(MessageFormat.format("Total rows: {0,number,integer}", rowCount));
		return allData;
	}

	private ExcelData getDataFromTab(Sheet sheet) {
		ExcelData data = new ExcelData();
		int cellLength = 0;
		// Set maximum with
		for ( Row row: sheet ) {
			if ( row.getLastCellNum() > cellLength ) {
				cellLength = row.getLastCellNum();
			}
		}
		List<String> cols = new ArrayList<String>();
		for (int i = 0; i < cellLength; i++) {
			cols.add("Column " + (i+1));
		}
		data.setTabName(sheet.getSheetName());
		data.setColumns(cols.toArray(new String[cols.size()]));
		for (Row row : sheet) {
			List<String> rowData = new ArrayList<String>();
			for (int i = 0; i < cellLength; i++) {
				if ( row.getCell(i) != null ) {
					Cell cell = row.getCell(i);
					switch (cell.getCellType()) {
						case Cell.CELL_TYPE_STRING:
							rowData.add(cell.getRichStringCellValue().getString());
							break;
						case Cell.CELL_TYPE_NUMERIC:
							if (DateUtil.isCellDateFormatted(cell)) {
								rowData.add(cell.getDateCellValue().toString());
							} else {
								rowData.add(String.valueOf(cell.getNumericCellValue()));
							}
							break;
						case Cell.CELL_TYPE_BOOLEAN:
							rowData.add(String.valueOf(cell.getBooleanCellValue()));
							break;
						case Cell.CELL_TYPE_FORMULA:
							rowData.add(cell.getCellFormula());
							break;
						default:
							rowData.add("");
					}
				} else {
					rowData.add("");
				}
			}
			if (rowData.size() > 0 && !isAllEmpty(rowData)) {
				data.add(rowData.toArray(new String[rowData.size()]));
			}
		}
		logger.debug(MessageFormat.format("TabName: [{0}], row length: {1,number,integer}, records: {2,number,integer}", sheet.getSheetName(), cellLength, data.getData().size()));
		return data;
	}

	private boolean isAllEmpty(List<String> rowData) {
		for (String s : rowData) {
			if (!s.equals("")) {
				return false;
			}
		}
		return true;
	}

}
